﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelToData
{
    internal class ExcelHelper
    {
        public static DataTable ExcelToDataTable(string filePath)
        {
            IWorkbook Workbook;
            DataTable table = new DataTable();
            try
            {
                using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(filePath).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            //定位在第一个sheet
            ISheet sheet = Workbook.GetSheetAt(0);
            //第一行为标题行
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            int rowCount = sheet.LastRowNum;

            //循环添加标题列
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) != null)
                {
                    headerRow.GetCell(i).SetCellType(CellType.String);
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    if (table.Columns.Contains(column.ColumnName))
                    {
                        //string uuid=Guid.NewGuid().ToString("N");
                        //table.Columns.Add(column+"_"+uuid);
                        table.Columns.Add(column + "_" + i.ToString());
                    }
                    else
                    { 
                        table.Columns.Add(column);
                    }
                }
            }
            //数据
            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                if (row != null)
                {
                    for (int columnIndex = 0; columnIndex < row.LastCellNum; columnIndex++)
                    {
                        ICell cell = row.GetCell(columnIndex);

                        //如果表格里面的值为空就输出空字符串
                        if (cell == null)
                        {
                            dataRow[columnIndex] = "";
                        }
                        else
                        {
                            if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                            {
                                if (cell.DateCellValue.Hour > 0 || cell.DateCellValue.Minute > 0 || cell.DateCellValue.Second > 0 || cell.DateCellValue.Millisecond > 0)
                                {
                                    dataRow[columnIndex] = cell.DateCellValue.ToString();
                                }
                                else
                                {
                                    //转换为时间的形式
                                    dataRow[columnIndex] = cell.DateCellValue.ToString("yyyy-MM-dd");
                                }
                            }
                            else
                            {                               
                                dataRow[columnIndex] = cell.ToString();
                            }
                        }
                    }
                    //保存
                    table.Rows.Add(dataRow);
                }
            }
            return table;
        }

        public static DataSet ExcelToDataSet(string filePath)
        {
            IWorkbook Workbook;
            DataSet ds = new DataSet();            
            try
            {
                using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(filePath).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            int sheetnumber = Workbook.NumberOfSheets;
            for (int si = 0; si < sheetnumber; si++)
            {
                try
                {
                    DataTable table = new DataTable();

                    //定位在第一个sheet
                    ISheet sheet = Workbook.GetSheetAt(si);

                    //给表赋值表名
                    table.TableName = sheet.SheetName;

                    //第一行为标题行
                    IRow headerRow = sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    int rowCount = sheet.LastRowNum;

                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        if (headerRow.GetCell(i) != null)
                        {
                            headerRow.GetCell(i).SetCellType(CellType.String);
                            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);                           
                            if (table.Columns.Contains(column.ColumnName))
                            {
                                //string uuid = Guid.NewGuid().ToString("N");
                                //table.Columns.Add(column + "_" + uuid);
                                table.Columns.Add(column + "_" + i.ToString());

                            }
                            else
                            {
                                table.Columns.Add(column);
                            }
                        }
                    }
                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null)
                        {
                            for (int columnIndex = 0; columnIndex < row.LastCellNum; columnIndex++)
                            {
                                ICell cell = row.GetCell(columnIndex);

                                //如果表格里面的值为空就输出空字符串
                                if (cell == null)
                                {
                                    dataRow[columnIndex] = "";
                                }
                                else
                                {
                                    if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                                    {
                                        if (cell.DateCellValue.Hour > 0 || cell.DateCellValue.Minute > 0 || cell.DateCellValue.Second > 0 || cell.DateCellValue.Millisecond > 0)
                                        {
                                            dataRow[columnIndex] = cell.DateCellValue.ToString();
                                        }
                                        else
                                        {
                                            //转换为时间的形式
                                            dataRow[columnIndex] = cell.DateCellValue.ToString("yyyy-MM-dd");
                                        }
                                    }
                                    else
                                    {
                                        dataRow[columnIndex] = cell.ToString();
                                    }
                                }
                            }
                            //保存                        
                            table.Rows.Add(dataRow);
                        }
                    }
                    ds.Tables.Add(table);
                }catch(Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
            }            
            return ds;
        }
    }
}
